package com.exam.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import com.exam.model.QuestionDbModel;

public class QuestionDbDao extends BaseDao{
	//管理员
	String sql = "";
	
	/**
	 * 获取题库列表
	 * @param admin
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<QuestionDbModel> getListByLimit(String limit) throws SQLException{
		
		ArrayList<QuestionDbModel> list = new ArrayList<QuestionDbModel>();
		
		sql = "select id," +
				"db_name," +
				"db_info," +
				"db_user," +
				"status " +
				" from ex_question_db where db_user='"+getAdmin()+"' "+limit;
		System.out.println(sql);
		res = query(sql);
		
		while(res.next()){
			QuestionDbModel qdm = new QuestionDbModel();
			qdm.setId(res.getInt(1));
			qdm.setDb_name(res.getString(2));
			qdm.setDb_info(res.getString(3));
			qdm.setDb_user(res.getString(4));
			qdm.setStatus(res.getInt(5));
			list.add(qdm);
		}
		
		closeDB();
		return list;
	}
	
	
	/**
	 * 获取题库列表
	 * @param admin
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<QuestionDbModel> getDBList() throws SQLException{
		
		ArrayList<QuestionDbModel> list = new ArrayList<QuestionDbModel>();
		
		sql = "select id," +
				"db_name" +
				" from ex_question_db where db_user='"+getAdmin()+"'";
		System.out.println(sql);
		res = query(sql);
		
		while(res.next()){
			QuestionDbModel qdm = new QuestionDbModel();
			qdm.setId(res.getInt(1));
			qdm.setDb_name(res.getString(2));
			list.add(qdm);
		}
		
		closeDB();
		return list;
	}
	
	/**
	 * 添加一条题库
	 * @param qdm
	 * @return
	 */
	public boolean addOneQuestionDB(QuestionDbModel qdm){
		sql = "insert into ex_question_db ( " +
				"db_name," +
				"db_info," +
				"db_user," +
				"status ) values( " +
				"'"+qdm.getDb_name()+"'," +
						"'"+qdm.getDb_info()+"'," +
								"'"+qdm.getDb_user()+"'," +
										""+qdm.getStatus()+" ) ";
		return adu(sql);
	}
	
	
	/**
	 * 获取题库信息
	 * @param dbid
	 * @return
	 * @throws SQLException
	 */
	public QuestionDbModel getOneQDB(String dbid) throws SQLException{
		sql = "select id,db_name,db_info,db_user,status from ex_question_db where id="+dbid;
		res = query(sql);
		QuestionDbModel qdm = null;
		if(res.next()){
			qdm = new QuestionDbModel();
			qdm.setId(res.getInt(1));
			qdm.setDb_name(res.getString(2));
			qdm.setDb_info(res.getString(3));
			qdm.setDb_user(res.getString(4));
			qdm.setStatus(res.getInt(5));
		}
		closeDB();
		return qdm;
	}
	
	
	/**
	 * 更新一条题库
	 * @param qdm
	 * @return
	 */
	public boolean updateOneQDB(QuestionDbModel qdm){
		sql = "update ex_question_db set " +
				"db_name='"+qdm.getDb_name()+"'," +
				"db_info='"+qdm.getDb_info()+"'," +
				"status="+qdm.getStatus()+" where id="+qdm.getId();
		return adu(sql);
	}
	
	
	/***
	 * 1.删除一个题库
	 * 2.删除题库的所有试题
	 * 3.删除试题的选项
	 * @param dbid
	 * @return
	 */
	public boolean deleteOneQDB(String dbid){
		//删除题库
		String sql3 = "delete from ex_question_db where id="+dbid;
		adu(sql3);
		//删除选项
		String sql1 = "delete from ex_question_options where qid in (select id from ex_question where dbid="+dbid+") ";
		//删除试题
		String sql2 = "delete from ex_question where dbid="+dbid;
		return adu(sql1)&&adu(sql2);
	}
	
	/**
	 * 更改状态
	 * @param dbid
	 * @param status
	 * @return
	 */
	public boolean changeStatus(String dbid , int status){
		String sql = "update ex_question_db set status="+status +" where id="+dbid;
		return adu(sql);
	}
	
	/**
	 * 获取记录总数
	 * @return
	 */
	public int getCount(){
		return super.getCount("ex_question_db");
	}
	
}
